import psycopg2
import os
import pandas as pd
import numpy as np

pointData=pd.read_csv(r'D:\大四上学期\GIS开发实习\flask练习\bike_pgSQL\baiduStreetPhoto\point_wgs.csv')
pointID=pointData['ID'].tolist()
pointx=np.array(pointData['pointX'])
pointy=np.array(pointData['pointY'])

pointCoord=np.array(pointData[['ID','pointX','pointY']])

print(pointID.index(1),pointx[pointID.index(1)],pointy[pointID.index(1)])

def dump_image():
    """
    要求:
    表已经存在
    path路径及文件名不允许有中文
    创建表时存文件的字段应该指定bytea类型,例如：create table image (id serial, data bytea);
    """
    # 将path目录下的所有图片导入PG
    path = r'D:\大四上学期\GIS开发实习\flask练习\bike_pgSQL\baiduStreetPhoto\pics'  # 反斜杠(\)转义输出，或写成 r'D:\huge\'
    conn = psycopg2.connect(database='bike', user='postgres', password='123456789',
                            host='localhost', port='5432')
    cur = conn.cursor()
    for dirpath, dirnames, filenames in os.walk(path):
        count=-1
        photopath=[]
        for filepath in filenames:
            # 字符串拼接成需要的格式
            file_path = "'" + path+filepath + "'"
            #计数 7张图片
            # with open(os.path.join(dirpath, filepath),'rb') as reader:
            #     img_buffer = reader.read()
            # print(os.path.join(dirpath, filepath),dirpath[58:], pointCoord[int(dirpath[58:])-1][1],pointCoord[int(dirpath[58:])-1][2])
            # sql = "insert into streetphoto(id,x,y,img_0) values(%s,%s,%s,%s)" #.format(pointCoord[int(dirpath[58:])-1][1], pointCoord[int(dirpath[58:])-1][2],psycopg2.Binary(img_buffer))
            # param=(int(dirpath[58:]),pointCoord[int(dirpath[58:])-1][1], pointCoord[int(dirpath[58:])-1][2],psycopg2.Binary(img_buffer))
            # #cur.execute('''insert into image(data) select pg_read_binary_file({});'''.format(file_path))
            # cur.execute(sql,param)
            # conn.commit()
            # conn.close()
            # return

#           存图片路径
            photopath.append(os.path.join(dirpath[53:], filepath))
            count+=1
            print(photopath[count])
            if count==7:
                # 返回ID索引，获取经纬度坐标
                sql = "insert into streetphoto(id,x,y,img_0,img_135,img_180,img_225,img_270,img_315,img_45,img_90)" \
                      "values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                param=(int(dirpath[58:]),
                       pointx[pointID.index(int(dirpath[58:]))],
                       pointy[pointID.index(int(dirpath[58:]))],
                       photopath[0],
                       photopath[1],
                       photopath[2],
                       photopath[3],
                       photopath[4],
                       photopath[5],
                       photopath[6],
                       photopath[7],
                       )
                cur.execute(sql,param)
                count=-1
                photopath=[]
    conn.commit()
    conn.close()


if __name__ == '__main__':

    dump_image()